package com.hnkywl.sts.dao.cggl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.cggl.Cgd;
import com.hnkywl.sts.entity.cggl.Cgdmx;
import com.hnkywl.sts.entity.cggl.Glfy;
import com.hnkywl.sts.entity.xsgl.Kh;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.criterion.*;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-7-7
 * Time: 下午3:14
 * To change this template use File | Settings | File Templates.
 */

@Repository
public class NbcgdDao extends SimpleHibernateTemplate<Cgd, Long> {
    private CgdmxDao cgdmxDao;

    public Page<Cgd> findAllCgdList(String cgdh, Date fhrqStart, Date fhrqEnd, String cglx, Long cggsId, Long cgbmId, Long cgrId, String zt, String sftzd, String htType, int pageNo, int pageSize) throws ParseException {
        Page<Cgd> page = new Page<Cgd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cggs", "cggs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgbm", "cgbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgr", "cgr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("cgsl").as("cgsl"))
                        .add(Property.forName("cgje").as("cgje"))
                        .add(Property.forName("cgbhsje").as("cgbhsje"))
                        .add(Property.forName("glfyje").as("glfyje"))
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("ysh").as("ysh"))
                        .add(Property.forName("gys.id").as("gys_id"))
                        .add(Property.forName("gys.qc").as("gys_qc"))
                        .add(Property.forName("gys.jc").as("gys_jc"))
                        .add(Projections.sqlProjection("cgdh as cgdh", new String[]{"cgdh"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("fhrq as fhrq", new String[]{"fhrq"}, new Type[]{Hibernate.DATE}))
                        .add(Projections.sqlProjection("cgsj as cgsj", new String[]{"cgsj"}, new Type[]{Hibernate.DATE}))
                        .add(Property.forName("sbgs.id").as("sbgs_id"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.id").as("sbbm_id"))
                        .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                        .add(Property.forName("sbr.id").as("sbr_id"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("cggs.id").as("cggs_id"))
                        .add(Property.forName("cggs.qc").as("cggs_qc"))
                        .add(Property.forName("cggs.jc").as("cggs_jc"))
                        .add(Property.forName("cgbm.id").as("cgbm_id"))
                        .add(Property.forName("cgbm.qc").as("cgbm_qc"))
                        .add(Property.forName("cgr.id").as("cgr_id"))
                        .add(Property.forName("cgr.xm").as("cgr_xm"))
                        .add(Property.forName("createUser.xm").as("createUser_xm"))
                        .add(Projections.sqlProjection("sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("lastUpdated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(cgdh)) {
            criteria.add(Property.forName("cgdh").like(cgdh, MatchMode.START));
        }
        if (fhrqStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhrqStart)));
        } else {
            Date start = sDaysAfterNowDate(new Date());
            criteria.add(Restrictions.ge("dateCreated", start));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhrqEnd)));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            criteria.add(Restrictions.le("dateCreated", end));
        }
        if (StringUtils.isNotEmpty(zt)) {
            if (zt.equals("ybc")) {
                criteria.add(Restrictions.eq("zt", "ybc"));
                criteria.add(Restrictions.or(Restrictions.isNull("yrk"),Restrictions.eq("yrk", false)));
                criteria.add(Restrictions.or(Restrictions.isNull("yjs"),Restrictions.eq("yjs", false)));
            } else if (zt.equals("yrk")) {
                criteria.add(Restrictions.eq("yrk", true));
            } else if (zt.equals("yjs")) {
                criteria.add(Restrictions.eq("yjs", true));
            }
        }

        if (StringUtils.isNotEmpty(htType) && htType.equals("bght")) {
            criteria.add(Restrictions.eq("cglx", htType));
        } else {
            criteria.add(Restrictions.ne("cglx", "bght"));
        }

        if (StringUtils.isNotEmpty(cglx)) {
            criteria.add(Restrictions.eq("cglx", cglx));
        }

        if (cggsId != null) {
            criteria.add(Restrictions.eq("cggs.id", cggsId));
        }
        if (cgbmId != null) {
            criteria.add(Restrictions.eq("cgbm.id", cgbmId));
        }
        if (cgrId != null) {
            criteria.add(Restrictions.eq("cgr.id", cgrId));
        }
        if (sftzd != null) {
            criteria.add(Restrictions.eq("sftzd", false));
        } else {
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }

        createCgdDataRegion(criteria);
        criteria.add(Restrictions.eq("cglx", "nbcg"));
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    private void createCgdDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        SimpleExpression se = null;
        switch (YgService.getCurrentYgCxfw()) {
            case 0:
                return;
            case 1:
                se = Restrictions.eq("createUser.zggs.id", YgService.getCurrentYgZgGsId());
                break;
            case 2:
                se = Restrictions.eq("createUser.zgjg.id", YgService.getCurrentYgGsId());
                break;
            case 3:
                se = Restrictions.eq("createUser.id", YgService.getCurrentYg().getId());
                break;
        }

        criteria.add(
                Restrictions.or(se,
                        Restrictions.and(Restrictions.eq("sbgs.id", YgService.getCurrentYgZgGsId()),
                                Restrictions.eq("zt", "ysp")
                        )
                ));
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    public Page<Cgd> findByYtjQuery(String zt, String lx, Long cggsId, Long cgbmId, Long cgrId, String cgdh, String wzbm, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Cgd> page = new Page<Cgd>(pageNo, pageSize, 0);
        //=====================================
        if (cgdh != null && !cgdh.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cgdh like ? ");
            param.add("%" + cgdh.trim() + "%");
        }
        if (zt != null && !zt.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zt = ?");
            param.add(zt);
        }
        if (lx != null && !lx.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cglx = ?");
            param.add(lx);
        }
        if (cggsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cggs.id = ? ");
            param.add(cggsId);
        }
        if (cgbmId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cgbm.id = ? ");
            param.add(cgbmId);
        }
        if (cgrId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cgr.id = ? ");
            param.add(cgrId);
        }

        if (wzbm != null && !wzbm.isEmpty()) {
            String ids = findCgdIdsByCgdmxs(wzbm);
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append("id in (" + ids + ")");
        }
        //==============记录数量===============
        hql = "select count(*) from Cgd where zt!='ybc' and (cglx!='zddh' or (cglx='zddh' and cght.rzlx='2')) " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Cgd where zt!='ybc' and (cglx!='zddh' or (cglx='zddh' and cght.rzlx='2')) " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql += " order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public Page<Cgd> findCgjlQuery(Long cghtId, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Cgd> page = new Page<Cgd>(pageNo, pageSize, 0);

        if (cghtId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cght.id = ? ");
            param.add(cghtId);
        }
        //==============记录数量===============
        hql = "select count(*) from Cgd where 1 =1 " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Cgd where 1=1  " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public String findCgdIdsByCgdmxs(String wzbm) {
        List<Cgdmx> cgdmxes = this.cgdmxDao.findCgdIdsByWzbm(wzbm);
        String ids = "";
        for (Cgdmx cgdmx : cgdmxes) {
            ids += cgdmx.getCgd().getId() + ",";
        }
        ids = ids.substring(0, ids.length() - 1);
        return ids;
    }


    /**
     * 条件查询采购单
     *
     * @param cgdh
     * @param fhrqStart
     * @param fhrqEnd
     * @param cgjsdId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Cgd> findCgdSearch(String cgdh, Date fhrqStart, Date fhrqEnd, Long cgjsdId, Long cggsId, Long gysId, int pageNo, int pageSize) {
        Page<Cgd> page = new Page<Cgd>();
        page.setAutoCount(false);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria countCriteria = getSession().createCriteria(this.entityClass)
                .setProjection(Projections.rowCount());

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cggs", "cggs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmxList", "cgdmxList")
                .setProjection(Projections.projectionList()
                        .add(Projections.sum("cgdmxList.sbsl").as("sbsl"))
                        .add(Projections.sum("cgdmxList.hsj").as("hsj"))
                        .add(Projections.sum("cgdmxList.bhsj").as("bhsj"))
                        .add(Projections.groupProperty("id").as("id"))
                        .add(Projections.groupProperty("cght.id").as("cght_id"))
                        .add(Projections.sqlGroupProjection("cgdh as cgdh", "cgdh", new String[]{"cgdh"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlGroupProjection("fhrq as fhrq", "fhrq", new String[]{"fhrq"}, new Type[]{Hibernate.DATE}))
                        .add(Projections.groupProperty("gys.qc").as("gys_qc"))
                        .add(Projections.groupProperty("dsyfje").as("dsyfje"))
                        .add(Projections.groupProperty("sbgs.id").as("sbgs_id"))
                        .add(Projections.groupProperty("sbgs.jc").as("sbgs_jc"))
                        .add(Projections.groupProperty("sbbm.id").as("sbbm_id"))
                        .add(Projections.groupProperty("sbbm.qc").as("sbbm_qc"))
                        .add(Projections.groupProperty("sbr.id").as("sbr_id"))
                        .add(Projections.groupProperty("sbr.xm").as("sbr_xm"))
                        .add(Projections.groupProperty("cggs.id").as("cggs_id"))
                        .add(Projections.groupProperty("cggs.qc").as("cggs_qc"))
                        .add(Projections.groupProperty("cggs.jc").as("cggs_jc"))
                        .add(Projections.sqlGroupProjection("this_.zt as zt", "this_.zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.groupProperty("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(cgdh)) {
            criteria.add(Property.forName("cgdh").like(cgdh, MatchMode.START));
            countCriteria.add(Property.forName("cgdh").like(cgdh, MatchMode.START));
        }
        if (fhrqStart != null) {
            criteria.add(Restrictions.gt("fhrq", fhrqStart));
            countCriteria.add(Restrictions.gt("fhrq", fhrqStart));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.lt("fhrq", fhrqEnd));
            countCriteria.add(Restrictions.lt("fhrq", fhrqEnd));
        }
        if (gysId != null) {
            criteria.add(Restrictions.eq("gys.id", gysId));
            countCriteria.add(Restrictions.eq("gys.id", gysId));
        }
        if (cggsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", cggsId));
            countCriteria.add(Restrictions.eq("sbgs.id", cggsId));
        }
        if (cgjsdId != null) {
            criteria.add(Restrictions.eq("cgjsd.id", cgjsdId));
            countCriteria.add(Restrictions.eq("cgjsd.id", cgjsdId));
        } else {
            DetachedCriteria dc = DetachedCriteria.forClass(Cgdmx.class);
            dc.setProjection(Projections.property("cgd.id"));
            dc.add(Restrictions.isNull("cgjsd"));
            criteria.add(Subqueries.propertyIn("id", dc));
            countCriteria.add(Subqueries.propertyIn("id",dc));
//            criteria.add(Restrictions.eq("zt", "ysp"));
//            countCriteria.add(Restrictions.eq("zt", "ysp"));
        }
        criteria.add(Restrictions.eq("sftzd", false));
        countCriteria.add(Restrictions.eq("sftzd", false));
        RegionDao.createDataRegion(criteria);
        RegionDao.createDataRegion(countCriteria);
        Page list = this.findByCriteria(page, criteria);
        Long totalCount = (Long) countCriteria.uniqueResult();
        list.setTotalCount(totalCount.intValue());
        return list;
    }

    public List<Map> findCgmx(Long id) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                        .add(Property.forName("gg.id").as("wzgg.id"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Projections.sqlProjection("gg1_.cz||' '||gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("rkjs").as("rkjs"))
                        .add(Property.forName("rkjz").as("rkjz"))
                        .add(Property.forName("rksl").as("rksl"))
                        .add(Property.forName("rkhsj").as("rkhsj"))
                        .add(Property.forName("th").as("th"))
                        .add(Property.forName("kh.id").as("kh.id"))
                        .add(Property.forName("kh.khbm").as("kh.khbm"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("cgd.id", id))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }


    public List<Map> findCgjsdSearchCgmx(Long id) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgjsd", "cgjsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                        .add(Property.forName("gg.id").as("wzgg.id"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Projections.sqlProjection("gg1_.cz||' '||gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("rkjs").as("rkjs"))
                        .add(Property.forName("rkjz").as("rkjz"))
                        .add(Property.forName("rksl").as("rksl"))
                        .add(Property.forName("rkhsj").as("rkhsj"))
                        .add(Property.forName("th").as("th"))
                        .add(Property.forName("kh.id").as("kh.id"))
                        .add(Property.forName("kh.khbm").as("kh.khbm"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("cgd.id", id)).add(Restrictions.isNull("cgjsd.id"))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }


    public List<Map> findCgfy(Long id) {
        Criteria criteria = getSession().createCriteria(Glfy.class)
                .createAlias("sydw", "sydw", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fylb", "fylb", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("sydw.id").as("sydw.id"))
                        .add(Property.forName("sydw.qc").as("sydw.qc"))
                        .add(Property.forName("fylb.id").as("fylb.id"))
                        .add(Property.forName("fylb.fykmjc").as("fylb.fykmjc"))
                        .add(Property.forName("jfsl").as("jfsl"))
                        .add(Property.forName("dj").as("dj"))
                        .add(Property.forName("je").as("je"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("cgd.id", id))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> findCgdmxSearch(Long cgjsdId) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("cgd.id").as("cgd.id"))
                        .add(Property.forName("cgd.cgdh").as("cgdh"))
                        .add(Property.forName("gg.id").as("wzgg.id"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Projections.sqlProjection("gg1_.cz||' '||gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("hse").as("hse"))
                        .add(Property.forName("bhse").as("bhse"))
                        .add(Property.forName("jsbhse").as("je"))
                        .add(Property.forName("jsse").as("se"))
                        .add(Property.forName("jsce").as("ce"))
                ).add(Restrictions.eq("cgjsd.id", cgjsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return criteria.list();
    }

    public List<Map> findGysByCgd(Long gsjgId) {
        String hql = "select distinct gys.id as id,gys.jc as jc, qyfr.nsh as qyfr_nsh from Cgd cgd left join cgd.gys gys left join gys.qyfr qyfr where cgd.sbgs.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        query.setLong(0, gsjgId);
        return query.list();
    }

    /**
     * 首页显示采购单数
     *
     * @param zt
     * @return
     */
    public List findCgdList(String zt) {
        String joinTableSql = " left join t_sys_yg yg on yg.id=t.createuser_id ";
        String dataRegion = RegionDao.createSQLDataRegion(joinTableSql, "yg");
        String whereSql = " where t.deleted ='0' and t.zt=?";
        String sql = "select t.zt from t_biz_cgd t " +
                dataRegion + whereSql;
        return this.findBySQL(sql, zt);
    }

    public List<Map> findCgmxByKh(Kh kh) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("gg.id").as("wzgg.id"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Projections.sqlProjection("gg1_.cz||' '||gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("rkjs").as("rkjs"))
                        .add(Property.forName("rkjz").as("rkjz"))
                        .add(Property.forName("rksl").as("rksl"))
                        .add(Property.forName("kh.khbm").as("kh.khbm"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("kh.id", kh.getId()))
                .add(Restrictions.eq("cgd.deleted", 0))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }


    @Autowired
    public void setCgdmxDao(CgdmxDao cgdmxDao) {
        this.cgdmxDao = cgdmxDao;
    }
}

